package com.anji.plus.gaea.inf.module.dynamic.controller.dto;

import com.alibaba.fastjson.JSONArray;
import com.anji.plus.gaea.inf.constant.InfConstant;
import com.baomidou.mybatisplus.core.toolkit.StringUtils;
import lombok.Getter;
import lombok.ToString;
import org.springframework.util.CollectionUtils;

import java.util.*;
import java.util.regex.Matcher;

/**
 * @author weijiaming
 * @since 2021/3/29 18:11
 */
@Getter
@ToString
public class SqlModel {

    // 原始SQL语句
    private String sourceSql;
    // 动态条件
    private String dynamicCondition;
    // 最终SQL语句
    private String destSql;
    // 返回字段左边
    private String leftSqlResults = "select";
    // 返回字段
    private String sqlResults;
    // 返回字段右边
    private String rightSqlResults = "from";
    // 表名
    private String tableName;
    // 条件
    private String condition;

    public SqlModel(String sourceSql) {
        this(sourceSql, null);
    }

    public SqlModel(String sourceSql, String dynamicCondition) {
        this(sourceSql, dynamicCondition, null);
    }

    public SqlModel(String sourceSql, String dynamicCondition, String sqlResults) {
        this.sourceSql = sourceSql;
        this.dynamicCondition = dynamicCondition;
        this.sqlResults = sqlResults;
        this.parseSqlSegment();
    }

    private void parseSqlSegment() {
        if (StringUtils.isBlank(this.sourceSql)) {
            return;
        }
        String sqlSegment = this.sourceSql.trim();
        if (!sqlSegment.toLowerCase().startsWith(this.leftSqlResults)
                || !sqlSegment.toLowerCase().contains(this.rightSqlResults)) {
           return;
        }
        sqlSegment = sqlSegment.substring(this.leftSqlResults.length()).trim();
        if (sqlSegment.toLowerCase().startsWith("distinct")) {
            sqlSegment = sqlSegment.substring("distinct".length()).trim();
            this.leftSqlResults = "select distinct";
        }
        int indexStrFrom = sqlSegment.toLowerCase().indexOf(this.rightSqlResults);

        String sqlResults = this.sqlResults;
        if (StringUtils.isBlank(sqlResults)) {
            sqlResults = sqlSegment.substring(0, indexStrFrom).trim();
            this.sqlResults = sqlResults;
        }

        String tableNameCon = sqlSegment.substring(indexStrFrom + rightSqlResults.length() + 1).trim();
        String[] tableNameConArr = tableNameCon.split("\\s", 2);
        this.tableName = tableNameConArr[0].toLowerCase().trim();
        if (tableNameConArr.length == 2) {
            this.condition = tableNameConArr[1].trim();
        }

        /*if (!sqlResults.contains("*")) {
            StringBuilder sb = new StringBuilder(" ");
            Arrays.stream(sqlResults.split(","))
                    .filter(result -> StringUtils.isNotBlank(result))
                    .forEach(result -> {
                        if (result.toLowerCase().contains("as")) {
                            sb.append(result).append(", ");
                        } else {
                            sb.append(result).append(" as ").append(StringUtils.underlineToCamel(result)).append(", ");
                        }
                    });
            this.sqlResults = sb.substring(0, sb.lastIndexOf(","));
        } else if ("*".equals(sqlResults.trim())) {
            this.sqlResults = sqlResults;
            TableInfo tableInfo = TableInfoHelper.getTableInfos().stream()
                    .filter(tf -> tf.getTableName().toLowerCase().equals(this.tableName))
                    .findFirst().orElse(null);
            if (tableInfo != null) {
                StringBuilder fsb = new StringBuilder();
                for (TableFieldInfo tableFieldInfo : tableInfo.getFieldList()) {
                    fsb.append(tableFieldInfo.getColumn()).append(" as ").append(tableFieldInfo.getField().getName()).append(", ");
                }
                this.sqlResults = fsb.substring(0, fsb.lastIndexOf(","));
            }
        }*/
        this.getDestSql();
    }


    public void setSqlResults(String sqlResults) {
        this.sqlResults = sqlResults;
    }

    public String getDestSql() {
        StringBuilder sb = new StringBuilder();
        sb.append(this.leftSqlResults == null ? "" : this.leftSqlResults).append(" ")
                .append(this.sqlResults == null ? "" : this.sqlResults).append(" ")
                .append(this.rightSqlResults == null ? "" : this.rightSqlResults).append(" ")
                .append(this.tableName == null ? "" : this.tableName).append(" ")
                .append(this.condition == null ? "" : this.condition);
        this.destSql = sb.toString();
        return this.destSql;
    }

    public String getSqlSegment(Map<String, Object> requestParam) {
        if (requestParam == null || requestParam.size() < 1
                || StringUtils.isBlank(this.sourceSql)
                || StringUtils.isBlank(this.dynamicCondition)) {
            return this.getDestSql();
        }
        StringBuffer sb = new StringBuffer(this.getDestSql());
        for (String everyCon : this.dynamicCondition.split(",,,")) {
            String condi = everyCon;
            Matcher matcherPrepared = InfConstant.PREPARED_SQL_PATTERN.matcher(condi);
            List<String> everyPreparedList = new ArrayList<>();
            while (matcherPrepared.find()) {
                String paramName = matcherPrepared.group(1);
//                String paramName = condi.substring(matcherPrepared.start() + 2, matcherPrepared.end() - 1);
                everyPreparedList.add(paramName);
            }
            // 根据入参判断条件是否需要
            if (!CollectionUtils.isEmpty(everyPreparedList)) {
                boolean present = everyPreparedList.stream().filter(ep -> {
                    Object obj = requestParam.get(ep);
                    if (obj == null) {
                        return true;
                    }
                    if (obj instanceof String) {
                        if (StringUtils.isBlank(String.valueOf(obj))) {
                            return true;
                        }
                    }
                    if (obj instanceof JSONArray) {
                        JSONArray jsonArray = (JSONArray) obj;
                        if(jsonArray.isEmpty()) {
                            return true;
                        }
                    }
                    return false;
                }).findAny().isPresent();
                if (present) {
                    continue;
                }
            }
            // 处理in
            Matcher matcherPreparedIn = InfConstant.PREPARED_IN_SQL_PATTERN.matcher(condi);
            List<String> preparedInList = new ArrayList();
            while (matcherPreparedIn.find()) {
//                int start = matcherPreparedIn.start();
//                String preparedInName = condi.substring(condi.indexOf("#{", start) + 2, matcherPreparedIn.end() - 1);
                String preparedInName = matcherPreparedIn.group(1);
                preparedInList.add(preparedInName);
            }
            if (!CollectionUtils.isEmpty(preparedInList)) {
                for (String preParedIn : preparedInList) {
                    String[] preParedInArr = requestParam.get(preParedIn).toString().split(",");
                    StringBuilder pisb = new StringBuilder("(");
                    for (int i = 0; i < preParedInArr.length; i++) {
                        String preParedInName = preParedIn + i;
                        while (requestParam.containsKey(preParedInName)) {
                            Random random = new Random(10);
                            preParedInName = preParedInName + random.nextInt();
                        }
                        pisb.append("#{").append(preParedInName).append("}").append(", ");
                        requestParam.put(preParedInName, preParedInArr[i]);
                    }
                    String preParedInNew = pisb.substring(0, pisb.lastIndexOf(",")) + ")";
                    System.out.println(preParedInNew);
                    condi = condi.replace("#{" + preParedIn + "}", preParedInNew);
                }
            }
            sb.append(" ").append(condi);
        }
        return sb.toString();
    }

    /*public static void main(String[] args) {
        Map<String, Object> param = new HashMap<>();
        param.put("appId", "gaea");
//        param.put("appName", "盖亚");
        param.put("status", "1,2,3");
        param.put("id", 1);
        String sql = "select distinct app_id, app_name as appName from gaea_inf_app where app_id = #{appId}";
        String dynamicCon = "and app_name = #{appName}, and status in #{status} and id = #{id}";
        SqlModel sqlModel = new SqlModel(sql, dynamicCon);
        System.out.println(sqlModel.getDestSql());
        System.out.println();
        String sqlSegment = sqlModel.getSqlSegment(param);
        System.out.println(sqlSegment);
    }*/

}
